package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import model.CourseScore;
import util.DBUtil;
import util.Page;
import vo.FindEmpTrain;
import dao.FindEmpTrainDAO;

public class FindEmpTrainDAOImpl implements FindEmpTrainDAO {

	public Page<FindEmpTrain> getAll(int pageNo, int pageSize) {
		Page<FindEmpTrain> page = new Page<FindEmpTrain>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<FindEmpTrain> list = new ArrayList<FindEmpTrain>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from FindEmpTrain";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from FindEmpTrain) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo()*pageSize);
			pstat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				FindEmpTrain fet=new FindEmpTrain();
				fet.setEmpid(rs.getInt("EID"));
				fet.setEmpno(rs.getString("EMPNO"));
				fet.setName(rs.getString("NAME"));
				fet.setTrainid(rs.getInt("TID"));
				fet.setTrainname(rs.getString("TRAINNAME"));
				list.add(fet);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<FindEmpTrain> getByTrainName(int pageNo, int pageSize,
			String trainname) {
		Page<FindEmpTrain> page = new Page<FindEmpTrain>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<FindEmpTrain> list = new ArrayList<FindEmpTrain>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from FindEmpTrain where trainname like'%"+trainname+"%'";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from FindEmpTrain where trainname like'%"+trainname+"%') s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo()*pageSize);
			pstat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				FindEmpTrain fet=new FindEmpTrain();
				fet.setEmpid(rs.getInt("EID"));
				fet.setEmpno(rs.getString("EMPNO"));
				fet.setName(rs.getString("NAME"));
				fet.setTrainid(rs.getInt("TID"));
				fet.setTrainname(rs.getString("TRAINNAME"));
				list.add(fet);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<FindEmpTrain> getByEmpNo(int pageNo, int pageSize, String empno) {
		Page<FindEmpTrain> page = new Page<FindEmpTrain>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<FindEmpTrain> list = new ArrayList<FindEmpTrain>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from FindEmpTrain where empno=?";
			pstat=conn.prepareStatement(sql);
			pstat.setString(1, empno);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from FindEmpTrain where empno=?) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, empno);
			pstat.setInt(2, page.getPageNo()*pageSize);
			pstat.setInt(3,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				FindEmpTrain fet=new FindEmpTrain();
				fet.setEmpid(rs.getInt("EID"));
				fet.setEmpno(rs.getString("EMPNO"));
				fet.setName(rs.getString("NAME"));
				fet.setTrainid(rs.getInt("TID"));
				fet.setTrainname(rs.getString("TRAINNAME"));
				list.add(fet);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<FindEmpTrain> getByEmpName(int pageNo, int pageSize,
			String empname) {
		Page<FindEmpTrain> page = new Page<FindEmpTrain>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<FindEmpTrain> list = new ArrayList<FindEmpTrain>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from FindEmpTrain where name like'%"+empname+"%'";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from FindEmpTrain where name like'%"+empname+"%') s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo()*pageSize);
			pstat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				FindEmpTrain fet=new FindEmpTrain();
				fet.setEmpid(rs.getInt("EID"));
				fet.setEmpno(rs.getString("EMPNO"));
				fet.setName(rs.getString("NAME"));
				fet.setTrainid(rs.getInt("TID"));
				fet.setTrainname(rs.getString("TRAINNAME"));
				list.add(fet);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}


}
